﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;

namespace NS
{
    [HotUpdate]
    [Description("采购订单执行情况报表")]
    public class ProcurementExecutionReport : SysReportBaseService
    {
        /// 初始化事件：在此事件中，设置报表基本属性
        public override void Initialize()
        {
            base.Initialize();
            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.BillKeyFieldName = "FID";
            // 报表名称
            this.ReportProperty.ReportName = new LocaleValue("采购订单执行情况报表", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = true;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            // 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
            this.ReportProperty.PrimaryKeyFieldName = "FID";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
            // 报表主键字段名：默认为FIDENTITYID，可以修改
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
        }

        public override string GetTableName()
        {
            var result = base.GetTableName();
            return result;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            // 拼接过滤条件 ： filter
            // 略
            // 默认排序字段：需要从filter中取用户设置的排序字段
            string seqFld = string.Format(base.KSQL_SEQ, " FID,FXH,FENTRYID");

            DateTime FStartDate;
            DateTime FEndDate;
            string FMaterialid = "";
            DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            string dyFilter1 = filter.FilterParameter.FilterString;
            StringBuilder SQLFilter = new StringBuilder();
            StringBuilder SQLFilter1 = new StringBuilder();
            StringBuilder SQLFilter2 = new StringBuilder();
            StringBuilder SQLFilter3 = new StringBuilder();
            FStartDate = Convert.ToDateTime(dyFilter["FStartDate"]);//开始日期
            FEndDate = Convert.ToDateTime(dyFilter["FEndDate"]);//最后发货日期
            //过滤 日期
            if (!FStartDate.ToString().Contains("0001") && !FEndDate.ToString().Contains("0001"))
            {
                SQLFilter.Append("and t.FDATE>='" + FStartDate + "' and t.FDATE<='" + FEndDate + "'");
            }
            if (!Convert.ToString(dyFilter["FMaterialid_id"]).Equals("0"))
            {
                SQLFilter1.Append("and t2.FMATERIALID='" + dyFilter["FMaterialid_id"] + "'");
            }
            if (!Convert.ToString(dyFilter["FSuppiler_id"]).Equals("0"))
            {
                SQLFilter1.Append("and t6.FSUPPLIERID='" + dyFilter["FSuppiler_id"] + "'");
            }
            if (!Convert.ToString(dyFilter["FOrgId_id"]).Equals("0"))
            {
                SQLFilter1.Append("and t.FPURCHASEORGID='" + dyFilter["FOrgId_id"] + "'");
            }
            if (!Convert.ToString(dyFilter["FBillNo"]).Equals(""))
            {
                SQLFilter1.Append("and t.FBillNo='" + dyFilter["FBillNo"] + "'");
            }
            if (!Convert.ToString(dyFilter1).Equals(""))
            {
                SQLFilter1.Append("and " + dyFilter1.Replace("FBILLNO", "T.FBILLNO") + "");
            }
            if (!Convert.ToString(dyFilter["FBillNo"]).Equals(""))
            {
                SQLFilter3.Append("and t.F_WNEG_TEXT2_SRC='" + dyFilter["FBillNo"] + "'");
            }

            // 取数SQL
            string sql = string.Format(@"/*dialect*/
---------------运费--------------
SELECT 
    ROW_NUMBER() OVER (PARTITION BY T.F_WNEG_TEXT2_SRC ORDER BY T.F_WNEG_TEXT2_SRC) AS RowNum, -- 添加序号
    T.F_WNEG_TEXT2_SRC,
    SUM(T.FYF) AS FYF, -- 汇总 FYF
    STRING_AGG(T.FYFFKRQ, ', ') AS FYFFKRQ,  
    STRING_AGG(T.FKFS, ', ') AS FKFS  into #YF
FROM (
    SELECT DISTINCT 
        t1.F_WNEG_TEXT2_SRC_E AS F_WNEG_TEXT2_SRC,
        t1.FPAYTOTALAMOUNTFOR AS FYF,
        t2.fid,
        MAX(CONVERT(VARCHAR(50), T2.FDATE, 23)) AS FYFFKRQ,
        t3.FDATAVALUE AS FKFS
    FROM 
        T_AP_PAYBILLSRCENTRY t
        LEFT JOIN T_AP_PAYBILLENTRY t1 ON t.FID = t1.FID
        LEFT JOIN T_AP_PAYBILL t2 ON t1.FID = t2.FID
        LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L t3 ON t1.F_WNEG_ASSISTANT = t3.FENTRYID
    WHERE 
        t1.FPURPOSEID = 242241 
        AND t1.F_WNEG_TEXT2_SRC_E <> ''
    GROUP BY 
        t1.F_WNEG_TEXT2_SRC_E, t1.FPAYTOTALAMOUNTFOR, t3.FDATAVALUE, t2.fid, T1.FPURPOSEID
) T
--WHERE 
--    F_WNEG_TEXT2_SRC = 'CGDD154855'
GROUP BY 
    T.F_WNEG_TEXT2_SRC;

select F_WNEG_TEXT2_SRC,SUM(FYF)FYF,SUM(FCGAMOUNT)FCGAMOUNT,SUM(FQTAMOUNT)FQTAMOUNT,FYFFKRQ,FKFS into #FKYQ from(
------------------------------运费------------------------------------------------------
--SELECT * FROM (
--select distinct t1.F_WNEG_TEXT2_SRC_E F_WNEG_TEXT2_SRC,t1.FPAYTOTALAMOUNTFOR FYF,0 FCGAMOUNT,0 FQTAMOUNT,t2.fid,
 --max(CONVERT(VARCHAR(50),T2.FDATE, 23))FYFFKRQ,
--t3.FDATAVALUE FKFS
--from T_AP_PAYBILLSRCENTRY t
--left join T_AP_PAYBILLENTRY t1 on t.FID=t1.FID
--left join T_AP_PAYBILL t2 on t1.FID=t2.FID
--left join T_BAS_ASSISTANTDATAENTRY_L t3 on t1.F_WNEG_ASSISTANT=t3.FENTRYID
--where t1.FPURPOSEID=242241 and t1.F_WNEG_TEXT2_SRC_E<>'' 
--group by t1.F_WNEG_TEXT2_SRC_E,t1.FPAYTOTALAMOUNTFOR,t3.FDATAVALUE,t2.fid,T1.FPURPOSEID
--)T

--union all
-----------------------------采购付款、预付款------------------------------------------------------
SELECT * FROM (
select distinct t1.F_WNEG_TEXT2_SRC_E F_WNEG_TEXT2_SRC,0 FYF,t1.FPAYTOTALAMOUNTFOR FCGAMOUNT,0 FQTAMOUNT,t2.fid,
max(CONVERT(VARCHAR(50),T2.FDATE, 23))FYFFKRQ,
t3.FDATAVALUE FKFS
from T_AP_PAYBILLSRCENTRY t
left join T_AP_PAYBILLENTRY t1 on t.FID=t1.FID
left join T_AP_PAYBILL t2 on t1.FID=t2.FID
left join T_BAS_ASSISTANTDATAENTRY_L t3 on t1.F_WNEG_ASSISTANT=t3.FENTRYID
where (t1.FPURPOSEID=20017 or t1.FPURPOSEID=20018)  and t.F_WNEG_TEXT2_SRC<>''  
group by t1.F_WNEG_TEXT2_SRC_E,t1.FPAYTOTALAMOUNTFOR ,t2.fid,t3.FDATAVALUE
)T

union all
------------------------------其他支出------------------------------------------------------
SELECT * FROM (
select distinct t1.F_WNEG_TEXT2_SRC_E,0 FYF,0 FCGAMOUNT,t1.FPAYTOTALAMOUNTFOR FQTAMOUNT,t2.fid,max(CONVERT(VARCHAR(50),T2.FDATE, 23))FYFFKRQ,
t3.FDATAVALUE FKFS
from T_AP_PAYBILLSRCENTRY t
left join T_AP_PAYBILLENTRY t1 on t.FID=t1.FID
left join T_AP_PAYBILL t2 on t1.FID=t2.FID
left join T_BAS_ASSISTANTDATAENTRY_L t3 on t1.F_WNEG_ASSISTANT=t3.FENTRYID
where t1.FPURPOSEID=20021  and t.F_WNEG_TEXT2_SRC<>'' 
group by t1.F_WNEG_TEXT2_SRC_E,t1.FPAYTOTALAMOUNTFOR ,t2.fid,t3.FDATAVALUE
)T
)T
GROUP BY F_WNEG_TEXT2_SRC,FYFFKRQ,FKFS


-----------------------------运费（取最新运费付款日期和付款方式）------------------------------------------------------
SELECT * into #FKYQ12 FROM (
select distinct t.F_WNEG_TEXT2_SRC, max(CONVERT(VARCHAR(50),T2.FDATE, 23))FYFFKRQ,
t3.FDATAVALUE FKFS

from T_AP_PAYBILLSRCENTRY t
left join T_AP_PAYBILLENTRY t1 on t.FID=t1.FID
left join T_AP_PAYBILL t2 on t1.FID=t2.FID
left join T_BAS_ASSISTANTDATAENTRY_L t3 on t1.F_WNEG_ASSISTANT=t3.FENTRYID
where t1.FPURPOSEID=242241 and t.F_WNEG_TEXT2_SRC<>'' 
group by t.F_WNEG_TEXT2_SRC,T2.FDATE,t3.FDATAVALUE,t.FSEQ
)T
SELECT F_WNEG_TEXT2_SRC,MAX(FYFFKRQ)FYFFKRQ,FKFS INTO #FKYQ1  FROM #FKYQ12
GROUP BY F_WNEG_TEXT2_SRC,FKFS
SELECT ROW_NUMBER() over( partition by T.F_WNEG_TEXT2_SRC order by  T.F_WNEG_TEXT2_SRC)FSEQ,T.F_WNEG_TEXT2_SRC,T.FCGAMOUNT,T.FQTAMOUNT,T.FYF,T1.FKFS,T1.FYFFKRQ INTO #FKYQ2 FROM #FKYQ T
LEFT JOIN #FKYQ1  T1 ON T.F_WNEG_TEXT2_SRC=T1.F_WNEG_TEXT2_SRC and t.FYFFKRQ=t1.FYFFKRQ and t.FKFS=T1.FKFS


-----------------组柜单------------------------
select * into #zg from (
select t.F_WLLZ_Date12 FYJDGRQ,t.F_WLLZ_Date13 FSJDGRQ,t.F_WLLZ_Text12 FGH,T1.F_WLLZ_Qty FZGSL,T3.FENTRYID,t1.FENTRYID ZGID from QZPV_t_Cust100001 t
left join QZPV_t_Cust_Entry100001 t1 on t.fid=t1.fid
left join QZPV_t_Cust_Entry100001_lk t2 on t1.FENTRYID=t2.FENTRYID
left join t_PUR_POOrderEntry t3 on t2.FSBillId=t3.FID and t2.FSId=t3.FENTRYID
left join t_PUR_POOrder t4 on t3.FID=t4.FID 
WHERE T3.FENTRYID IS NOT NULL 
union all
select t.F_WLLZ_Date12 FYJDGRQ,t.F_WLLZ_Date13 FSJDGRQ,t.F_WLLZ_Text12 FGH,T1.F_WLLZ_Qty FZGSL,T5.FENTRYID,t1.FENTRYID ZGID from QZPV_t_Cust100001 t
left join QZPV_t_Cust_Entry100001 t1 on t.fid=t1.fid
left join QZPV_t_Cust_Entry100001_lk t2 on t1.FENTRYID=t2.FENTRYID
left join T_PUR_ReceiveEntry t3 on t2.fsbillid=t3.fid and t2.fsid=t3.FENTRYID
left join T_PUR_ReceiveEntry_lk t4 on t3.FENTRYID=t4.FENTRYID
left join t_PUR_POOrderEntry t5 on t4.FSBILLID=t5.FID and t4.FSID=t5.FENTRYID
left join t_PUR_POOrder t6 on t5.FID=t6.FID
WHERE T5.FENTRYID IS NOT NULL
)t 
order by FSJDGRQ asc

SELECT 
--T1.FSEQ,
ROW_NUMBER() OVER (PARTITION BY t.fbillno ORDER BY t1.FSEQ,t2.fnumber ) AS FSEQ,
----------------------------------------------------采购订单单据头字段-----------------------------------------------------------
T.FID,T.FBILLNO,T4.FNAME FCGZZ,
CONVERT(VARCHAR(50),T.FDATE, 23) FCGRQ,T5.FNAME FCGY,T6.FNAME FWLGYS,
CONVERT(VARCHAR(50),t.F_WNEG__WLLZ_Date7, 23) F_WNEG__WLLZ_Date7
,T.FWNEGText5 FJSTDH, T.F_WNEG_Text51 FHTDH,T.F_WNEG_TEXT28 FCWBZ,T.F_WNEG_TEXT9 FDDLX,
T33.FNAME FSettleCurrId,
CASE WHEN T.F_WNEG_COMBO2='样品仓' then '样品仓'
WHEN T.F_WNEG_COMBO2='工厂整柜' then '工厂整柜'
WHEN T.F_WNEG_COMBO2='工厂组柜' then '工厂组柜'
WHEN T.F_WNEG_COMBO2='海狸散货柜' then '海狸散货柜'
ELSE  '第三方货代' END FZGLX

----------------------------------------------------采购订单单据体明细字段-----------------------------------------------------------
,t2.FNUMBER FWLBM,T7.FSPECIFICATION,T7.FNAME FWLMC,
case when t1.FQTY<>0 OR t1.FQTY IS NULL then t1.FQTY else 0 end FQTY,
case when t1.FWNEGQty2<>0 then t1.FWNEGQty2 else 0 end FWNEGQty2 ,
case when t1.F_WNEG_Decimal<>0 then t1.F_WNEG_Decimal else 0 end F_WNEG_Decimal,
case when t2.F_WLLZ_Qty2<>0 then t2.F_WLLZ_Qty2 else 0 end  FDGCPZL,
case when T3.FPRICE<>0 then T3.FPRICE else 0 end FPRICE,
case when T3.FTAXPRICE<>0 then T3.FTAXPRICE else 0 end FTAXPRICE,
case when T3.FALLAMOUNT<>0 then T3.FALLAMOUNT else 0 end  FALLAMOUNT_LC,
T1.FALLAMOUNTEXCEPTDISCOUNT as FALLAMOUNTEXCEPTDISCOUNT,
T21.FDATAVALUE FFKFS,T22.FDATAVALUE FBZ,T23.FNAME FCKFS,
CASE WHEN T24.FREMAINSTOCKINQTY<>0 THEN T24.FREMAINSTOCKINQTY ELSE 0 END  FREMAINSTOCKINQTY,
t1.F_WNEG_REMARKS3,t1.F_WNEG_REMARKS4,T1.FSEQ FXH,T1.F_WNEG_TEXT14,T1.F_WNEG_Text16,t1.FENTRYID,t3.FDISCOUNT
----------------------------------------------------组柜单-----------------------------------------------------------
,CONVERT(VARCHAR(50),T30.FSJDGRQ, 23)FSJDGRQ,
CONVERT(VARCHAR(50),T30.FYJDGRQ, 23)FYJDGRQ,T30.FGH,
case when T30.FZGSL<>0 then T30.FZGSL else 0 end FZGSL
----------------------------------------------------采购入库单-----------------------------------------------------------
,case when T12.FTAXPRICE<>0 then T12.FTAXPRICE else 0 end FRKHSDJ,
case when T3.FALLAMOUNT<>0 then (T3.FALLAMOUNT/t1.FQTY*T9.FREALQTY) else 0 end FRKJSHJ,
CONVERT(VARCHAR(200),T10.FDATE, 23) FRKRQ,
case when T9.FREALQTY<>0 then T9.FREALQTY else 0 end  FRKSL,
CASE WHEN t9.FENTRYID IS NOT NULL THEN t9.FENTRYID ELSE 0 END RKID,t9.F_WNEG_REMARKS1 FRKBZ
----------------------------------------------------付款单-----------------------------------------------------------
,t32.FYF,
t31.FCGAMOUNT,
T31.FQTAMOUNT,
(t31.FYF+t31.FCGAMOUNT+t31.FQTAMOUNT)FHJ,

CASE WHEN t32.FYF=0 THEN CONVERT(VARCHAR(500),t31.FYFFKRQ,120) ELSE CONVERT(VARCHAR(500),t32.FYFFKRQ,120) END FYFFKRQ,
CASE WHEN t32.FYF=0 THEN CONVERT(VARCHAR(500),t31.FKFS,120) ELSE CONVERT(VARCHAR(500),t32.FKFS,120) END FKFS


---------------------------------------------------------------------------------------------------------------------

into #A
FROM t_PUR_POOrder T
LEFT JOIN t_PUR_POOrderEntry t1 on t.fid=t1.fid
left join T_BD_MATERIAL t2 on t1.FMATERIALID=t2.FMATERIALID
left join t_PUR_POOrderEntry_f t3 on t1.fentryid=t3.fentryid
LEFT JOIN T_ORG_ORGANIZATIONS_L T4 ON T.FPURCHASEORGID=T4.FORGID
LEFT JOIN V_BD_BUYER_L t5 on t.FPURCHASERID=t5.fid
left join T_BD_SUPPLIER_L t6 on t.F_WNEG_Base3=t6.FSUPPLIERID
LEFT JOIN T_BD_MATERIAL_L T7 ON T2.FMATERIALID=T7.FMATERIALID
LEFT JOIN #zg T30 ON T1.FENTRYID=T30.FENTRYID
left join T_STK_INSTOCKENTRY_LK t8 on t1.FID=t8.FSBILLID and t1.FENTRYID=t8.FSID
left join T_STK_INSTOCKENTRY t9 on t8.FENTRYID=t9.FENTRYID
left join t_STK_InStock t10 on t9.FID=t10.FID
LEFT JOIN T_PUR_POORDERFIN T11 ON T.FID=T11.FID
LEFT JOIN T_STK_INSTOCKENTRY_F T12 ON T9.FENTRYID=T12.FENTRYID
left join T_BAS_ASSISTANTDATAENTRY_L T21 on t1.F_WNEG_ASSISTANT3=T21.FENTRYID
left join T_BAS_ASSISTANTDATAENTRY_L T22 on t1.F_WNEG_ASSISTANT4=T22.FENTRYID
left join T_BAS_PREBDTWO_L t23 on t1.F_WNEG_BASE=t23.FID
LEFT JOIN T_PUR_POORDERENTRY_R T24 ON T1.FENTRYID=T24.FENTRYID
left join #FKYQ2 t31 on t.fbillno=t31.F_WNEG_TEXT2_SRC and t1.fseq=t31.fseq
left join #YF t32 on t.fbillno=t32.F_WNEG_TEXT2_SRC and T1.FSEQ=T32.RowNum
LEFT JOIN T_BD_CURRENCY_L T33 ON T11.FSettleCurrId=T33.FCURRENCYID
WHERE T4.FLOCALEID=2052 {2} {3}
order by t.FID,t1.FENTRYID desc


SELECT distinct t.*,
0 JHFENTRYID,0 FYFAMOUNT,CONVERT(VARCHAR(500),'',120)FYJFKRQ,CONVERT(VARCHAR(500),'',120)FSJFKRQ,CONVERT(VARCHAR(500),'',120)FYFDH,
0 FKGLJE,0 FWFJE,CONVERT(VARCHAR(500),'',120)F_WNEG_TEXT5,CONVERT(VARCHAR(500),'',120)F_WNEG_TEXT22,CONVERT(VARCHAR(500),'',120)F_WNEG_Text36,
CONVERT(VARCHAR(500),'',120)F_WNEG_Date9,CONVERT(VARCHAR(500),'',120)F_WNEG_Text40,CONVERT(VARCHAR(500),'',120)F_WNEG_Assistant8,CONVERT(VARCHAR(500),'',120)F_WNEG_Assistant7,
CONVERT(VARCHAR(500),'',120)FCWZT,CONVERT(VARCHAR(500),'',120)FFKZH,CONVERT(VARCHAR(2000),'',120)FRKYF
INTO #B
FROM #A t

--where FBILLNO='CGDD122259'
--GROUP BY FID,FBILLNO,FCGZZ,FCGRQ,FCGY,FWLGYS,F_WNEG__WLLZ_Date7,FJSTDH,FHTDH,FCWBZ,FZGLX
--FWLBM,FSPECIFICATION,FWLMC,FQTY,FWNEGQTY2,F_WNEG_DECIMAL,FDGCPZL,FSJDGRQ,FYJDGRQ,FGH,FZGSL,FRKHSDJ,FRKJSHJ,FRKRQ,FRKSL,FYF,FCGAMOUNT,FQTAMOUNT,FHJ,FYFFKRQ,FKFS,FSEQ

SELECT SUM(FKGLJE)FKGLJE,SUM(FYFAMOUNT)FYFAMOUNT,FENTRYID,F_WNEG_TEXT2_SRC,F_WNEG_TEXT2_SRC_E into #FKGLJE FROM (
select (sum(t6.FREALPAYAMOUNTFOR)- sum(t6.FREFUNDAMOUNT)-sum(t6.F_WNEG_AMOUNT)-sum(t6.F_WNEG_AMOUNT1)-sum(t6.FHANDLINGCHARGEFOR))FKGLJE, 0 FYFAMOUNT,t.FENTRYID,t1.fbillno F_WNEG_TEXT2_SRC,t6.F_WNEG_TEXT2_SRC_E 
 from T_PUR_POORDERINSTALLMENT t
left join t_PUR_POOrder t1 on t.fid=t1.fid
left join T_AP_PAYBILLENTRY t6 on t.FID=t6.F_WNEG_Text10_SRC_E and t.FENTRYID=t6.F_WNEG_Text11_SRC_E
WHERE T6.FPURPOSEID<>'242241' and  T6.FPURPOSEID<>'20021' 
group by t.FENTRYID,t6.F_WNEG_TEXT2_SRC_E,t6.FMATERIALID,t1.fbillno
UNION ALL
select 0 FKGLJE, 0 FYFAMOUNT,t.FENTRYID, t1.fbillno F_WNEG_TEXT2_SRC, t1.fbillno F_WNEG_TEXT2_SRC_E 
from T_PUR_POORDERINSTALLMENT t
left join t_PUR_POOrder t1 on t.fid=t1.fid
LEFT JOIN T_CN_PAYAPPLYENTRY_LK T2 ON T.FID=T2.FSBILLID AND T.FENTRYID=T2.FSID
LEFT JOIN T_CN_PAYAPPLYENTRY T3 ON T2.FENTRYID=T3.FENTRYID
where  t3.fid NOT in 
(select FSBILLID FROM T_AP_PAYBILLSRCENTRY_LK )
--or  t3.fentryid NOT in 
--(select fsid FROM T_AP_PAYBILLSRCENTRY_LK )
UNION ALL 
SELECT 0 FKGLJE,T1.FYFAMOUNT ,T1.FENTRYID,t.fbillno F_WNEG_TEXT2_SRC,T.FBILLNO F_WNEG_TEXT2_SRC_E  FROM t_PUR_POOrder T
LEFT JOIN T_PUR_POORDERINSTALLMENT T1 ON T.FID=T1.FID
)T
GROUP BY FENTRYID,F_WNEG_TEXT2_SRC,F_WNEG_TEXT2_SRC_E

SELECT distinct FSEQ,  t.FID,CONVERT(VARCHAR(500),'',120) FBILLNO,CONVERT(VARCHAR(500),'',120) FCGZZ,CONVERT(VARCHAR(500),'',120) FCGRQ,CONVERT(VARCHAR(500),'',120) FCGY,
CONVERT(VARCHAR(500),'',120) FWLGYS,CONVERT(VARCHAR(500),'',120) F_WNEG__WLLZ_Date7,CONVERT(VARCHAR(500),'',120) FJSTDH,CONVERT(VARCHAR(500),'',120) FHTDH,CONVERT(VARCHAR(500),'',120) FCWBZ,CONVERT(VARCHAR(500),'',120)FZGLX,
CONVERT(VARCHAR(500),'',120) FWLBM,CONVERT(VARCHAR(500),'',120) FSPECIFICATION,CONVERT(VARCHAR(200),'',120) FWLMC,0 FQTY,0 FWNEGQTY2,0 F_WNEG_DECIMAL,0 FDGCPZL,0 FPRICE,0 FTAXPRICE,0 FALLAMOUNT_LC,0 FALLAMOUNTEXCEPTDISCOUNT,
CONVERT(VARCHAR(500),'',120) FFKFS,CONVERT(VARCHAR(500),'',120) FBZ,CONVERT(VARCHAR(500),'',120)FCKFS,0 FREMAINSTOCKINQTY,CONVERT(VARCHAR(500),'',120)F_WNEG_REMARKS3,CONVERT(VARCHAR(500),'',120)F_WNEG_REMARKS4,CONVERT(VARCHAR(500),'',120)FXH,CONVERT(VARCHAR(500),'',120)F_WNEG_Text14,CONVERT(VARCHAR(500),'',120)F_WNEG_Text16,0 FENTRYID,
CONVERT(VARCHAR(500),'',120) FSJDGRQ,CONVERT(VARCHAR(500),'',120) FYJDGRQ,CONVERT(VARCHAR(500),'',120) FGH,0 FZGSL,0 FRKHSDJ,CONVERT(VARCHAR(500),'',120) FRKBZ,
0 FRKJSHJ,CONVERT(VARCHAR(500),'',120) FRKRQ,0 FRKSL,0 RKID,0 FYF,0 FCGAMOUNT,0 FQTAMOUNT,0 FHJ,CONVERT(VARCHAR(500),'',120)FYFFKRQ,CONVERT(VARCHAR(500),'',120)FKFS , 
T.FENTRYID JHFENTRYID,T3.FYFAMOUNT,CONVERT(VARCHAR(50),F_WNEG_Date, 23) FYJFKRQ,CONVERT(VARCHAR(50),F_WNEG_Date1, 23) FSJFKRQ,FRelBillNo FYFDH,t3.FKGLJE,(isnull(T3.FYFAMOUNT,0)-isnull(t3.FKGLJE,0))FWFJE,t.F_WNEG_TEXT5,t.F_WNEG_TEXT22,F_WNEG_Text36,CONVERT(VARCHAR(50),F_WNEG_Date9, 23) F_WNEG_Date9,
CONVERT(VARCHAR(50),F_WNEG_Text40, 23)F_WNEG_Text40,T1.FDATAVALUE F_WNEG_Assistant8,T2.FDATAVALUE F_WNEG_Assistant7,t.F_WNEG_TEXT5 FCWZT,t.F_WNEG_Text22 FFKZH, CONVERT(VARCHAR(2000),F_WNEG_Text40, 23) FRKYF,0 FDISCOUNT
INTO #C 
FROM T_PUR_POORDERINSTALLMENT  T
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L T1 ON T.F_WNEG_Assistant8=T1.FENTRYID
LEFT JOIN T_BAS_ASSISTANTDATAENTRY_L T2 ON T.F_WNEG_Assistant7=T2.FENTRYID
left join t_PUR_POOrder t4 on t.fid=t4.fid
left join #FKGLJE t3 on t.fentryid=t3.fentryid and t4.fbillno=t3.F_WNEG_TEXT2_SRC_E
--and  t4.fbillno=t3.F_WNEG_TEXT2_SRC_E
--GROUP BY T.FID,T3.FYFAMOUNT,FENTRYID,FSEQ


select  t.fid into #D from(
SELECT MAX(FSEQ)BIGFSEQ,FID FROM #A
--where fid=119243
GROUP BY FID
)t
left join 
(
SELECT MAX(FSEQ)BIGFSEQ,FID FROM T_PUR_POORDERINSTALLMENT
--where fid=119243
GROUP BY FID
) t1 on t.fid=t1.FID
where t.BIGFSEQ>=t1.BIGFSEQ

select  t.fid into #E from(
SELECT MAX(FSEQ)BIGFSEQ,FID FROM #A
--where fid=119243
GROUP BY FID
)t
left join 
(
SELECT MAX(FSEQ)BIGFSEQ,FID FROM T_PUR_POORDERINSTALLMENT
--where fid=119243
GROUP BY FID
) t1 on t.fid=t1.FID
where t.BIGFSEQ<t1.BIGFSEQ

SELECT T1.FSEQ,T.FID,T.FBILLNO,T.FCGZZ,T.FCGRQ,T.FCGY,T.FDDLX,
T.FWLGYS,T.F_WNEG__WLLZ_Date7,T.FJSTDH,T.FHTDH,T.FCWBZ,T.FZGLX,T.FWLBM,
T.FSPECIFICATION,T.FWLMC,T.FQTY,T.FWNEGQty2,T.F_WNEG_Decimal,T.FDGCPZL,
T.FPRICE,T.FTAXPRICE,Convert(decimal(18,2),T.FALLAMOUNT_LC)FALLAMOUNT_LC,T.FALLAMOUNTEXCEPTDISCOUNT,T.FFKFS,T.FBZ,T.FCKFS,
T.FREMAINSTOCKINQTY,T.F_WNEG_REMARKS3,T.F_WNEG_REMARKS4,T.FXH,T.F_WNEG_TEXT14,
T.F_WNEG_TEXT16,T.FENTRYID,T.FSJDGRQ,T.FYJDGRQ,T.FGH,T.FZGSL,T.FRKHSDJ,T.FRKBZ,Convert(decimal(18,2),T.FRKJSHJ)FRKJSHJ,
T.FRKRQ,T.FRKSL,T.RKID,T.FYF,T.FCGAMOUNT,T.FQTAMOUNT,T.FHJ,T.FYFFKRQ,T.FKFS,T1.JHFENTRYID,Convert(decimal(18,2),T1.FYFAMOUNT)FYFAMOUNT,T1.FYJFKRQ,
T1.FSJFKRQ,T1.FYFDH,T1.FKGLJE,Convert(decimal(18,2),T1.FWFJE)FWFJE,T1.F_WNEG_TEXT5,T1.F_WNEG_TEXT22,T1.F_WNEG_Text36,
T1.F_WNEG_Date9,T1.F_WNEG_Text40,T1.F_WNEG_Assistant8,T1.F_WNEG_Assistant7,
T1.FCWZT,T1.FFKZH,T1.FRKYF,t.FDISCOUNT,t.FSettleCurrId INTO #F FROM #B T
LEFT JOIN #C T1 ON T.FID=T1.FID AND T.FSEQ=T1.FSEQ
WHERE T.FID IN (SELECT * FROM #D)
ORDER BY T.FID,T.FSEQ

SELECT T.FSEQ,T.FID,T1.FBILLNO,T1.FCGZZ,T1.FCGRQ,T1.FCGY,T1.FDDLX,
T1.FWLGYS,T1.F_WNEG__WLLZ_Date7,T1.FJSTDH,T1.FHTDH,T1.FCWBZ,T1.FZGLX,T1.FWLBM,
T1.FSPECIFICATION,T1.FWLMC,T1.FQTY,T1.FWNEGQty2,T1.F_WNEG_Decimal,T1.FDGCPZL,
T1.FPRICE,T1.FTAXPRICE,Convert(decimal(18,2),T1.FALLAMOUNT_LC)FALLAMOUNT_LC,T1.FALLAMOUNTEXCEPTDISCOUNT,T1.FFKFS,T1.FBZ,T1.FCKFS,
T1.FREMAINSTOCKINQTY,T1.F_WNEG_REMARKS3,T1.F_WNEG_REMARKS4,T1.FXH,T1.F_WNEG_TEXT14,
T1.F_WNEG_TEXT16,T1.FENTRYID,T1.FSJDGRQ,T1.FYJDGRQ,T1.FGH,T1.FZGSL,T1.FRKHSDJ,T1.FRKBZ,Convert(decimal(18,2),T1.FRKJSHJ)FRKJSHJ,
T1.FRKRQ,T1.FRKSL,T1.RKID,T1.FYF,T1.FCGAMOUNT,T1.FQTAMOUNT,T1.FHJ,T1.FYFFKRQ,T1.FKFS,T.JHFENTRYID,Convert(decimal(18,2),T.FYFAMOUNT)FYFAMOUNT,T.FYJFKRQ,
T.FSJFKRQ,T.FYFDH,T.FKGLJE,Convert(decimal(18,2),T.FWFJE)FWFJE,T.F_WNEG_TEXT5,T.F_WNEG_TEXT22,T.F_WNEG_Text36,
T.F_WNEG_Date9,T.F_WNEG_Text40,T.F_WNEG_Assistant8,T.F_WNEG_Assistant7,
T.FCWZT,T.FFKZH,T.FRKYF,t1.FDISCOUNT,t1.FSettleCurrId INTO #G
FROM #C T
LEFT JOIN #B T1 ON T.FID=T1.FID AND T.FSEQ=T1.FXH
WHERE T.FID IN (SELECT * FROM #E)
ORDER BY T.FID,T.FSEQ

UPDATE T SET
T.FBILLNO= T1.FBILLNO,
T.FCGZZ=T1.FCGZZ,
T.FCGRQ=T1.FCGRQ,
T.FCGY=T1.FCGY,
T.FWLGYS=T1.FWLGYS,
T.F_WNEG__WLLZ_Date7=T1.F_WNEG__WLLZ_Date7,
T.FJSTDH=T1.FJSTDH,
T.FHTDH=T1.FHTDH,
T.FCWBZ=T1.FCWBZ,
T.FZGLX=T1.FZGLX,
T.FDDLX=T1.FDDLX
FROM #G T
LEFT JOIN #B T1 ON T.FID=T1.FID 
WHERE T.FID IN (SELECT * FROM #E) AND T.FBILLNO IS NULL

SELECT ROW_NUMBER() over( partition by FENTRYID order by  FBILLNO)MXID,
ROW_NUMBER() over( partition by RKID order by  FBILLNO)RKMXID,*,
(ISNULL(FYF,0)+ISNULL(FQTAMOUNT,0)+ISNULL(FCGAMOUNT,0))FSUM,
Convert(decimal(18,2),(ISNULL(FREMAINSTOCKINQTY,0)*(ISNULL(FALLAMOUNT_LC,0)/FQTY)))FQHAMOUNT,Convert(decimal(18,2),0) FRefundAmount,CONVERT(VARCHAR(500),'',120)FRefundNOTE,
{0} INTO {1} FROM (
SELECT * FROM #F
UNION ALL
SELECT * FROM #G
)T 
                                        ", seqFld, tableName, SQLFilter, SQLFilter1, SQLFilter3);
            DBUtils.ExecuteDynamicObject(this.Context, sql);
            sql = string.Format(@"/*dialect*/UPDATE {0} SET FQTY=0,FWNEGQty2=0,FALLAMOUNT_LC=0,FALLAMOUNTEXCEPTDISCOUNT=0,FREMAINSTOCKINQTY=0,FQHAMOUNT=0,
FYF=0,FCGAMOUNT=0,FQTAMOUNT=0,FHJ=0,FYFFKRQ='',FKFS='',FSUM=0
            where mxid not in (select min(mxid) from {0} group by FBILLNO)", tableName);
            DBUtils.Execute(this.Context, sql);
            sql = string.Format(@"/*dialect*/UPDATE {0} SET FYFFKRQ='',FKFS=''
            where FYF=0", tableName);
            DBUtils.Execute(this.Context, sql);
            sql = string.Format(@"/*dialect*/UPDATE {0} SET 
FSJDGRQ='',FYJDGRQ='',FGH='',FZGSL=0,
FYJFKRQ='',FSJFKRQ='',FYFDH=''
            where mxid not in (select min(mxid) from {0} group by FBILLNO,FGH)", tableName);
            DBUtils.Execute(this.Context, sql);
            sql = string.Format(@"/*dialect*/UPDATE t SET 
t.FRefundAmount=t1.FREFUNDAMOUNTFOR,
T.FWFJE=T.FWFJE,
t.FRefundNOTE=t1.FNOTE,
t.FKGLJE=t.FKGLJE
from {0} t
left join (
select t.FBILLNO,t1.FPURCHASEORDERNO,t1.FREFUNDAMOUNTFOR,t1.FNOTE from T_AP_REFUNDBILL t
left join T_AP_REFUNDBILLENTRY t1 on t.fid=t1.fid
) 
T1 ON T.FYFDH LIKE '%' + t1.FBILLNO + '%' and t.FBILLNO=t1.FPURCHASEORDERNO
where t1.FREFUNDAMOUNTFOR is not null
", tableName);
            DBUtils.Execute(this.Context, sql);
            sql = string.Format(@"/*dialect*/UPDATE {0} SET 
                    FRKHSDJ=0,FRKJSHJ=0,FRKRQ='',FRKSL=0,FRKBZ=''
                    where  RKMXID<>'1' 
            ", tableName);
            DBUtils.Execute(this.Context, sql);
        }

        protected override string GetIdentityFieldIndexSQL(string tableName)
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }

        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }

        protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
        {
            var result = base.GetSummaryColumsSQL(summaryFields);
            return result;
        }

        protected override System.Data.DataTable GetListData(string sSQL)
        {
            var result = base.GetListData(sSQL);
            return result;
        }

        protected override System.Data.DataTable GetReportData(IRptParams filter)
        {
            var result = base.GetReportData(filter);
            return result;
        }

        protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
        {
            var result = base.GetReportData(tablename, filter);
            return result;
        }

        public override int GetRowsCount(IRptParams filter)
        {
            var result = base.GetRowsCount(filter);
            return result;
        }

        protected override string BuilderFromWhereSQL(IRptParams filter)
        {
            string result = base.BuilderFromWhereSQL(filter);
            return result;
        }

        protected override string BuilderSelectFieldSQL(IRptParams filter)
        {
            string result = base.BuilderSelectFieldSQL(filter);
            return result;
        }

        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string result = base.BuilderTempTableOrderBySQL(filter);
            return result;
        }

        public override void CloseReport()
        {
            base.CloseReport();
        }

        protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
        {
            string result = base.CreateGroupSummaryData(filter, tablename);
            return result;
        }

        protected override void CreateTempTable(string sSQL)
        {
            base.CreateTempTable(sSQL);
        }

        public override void DropTempTable()
        {
            base.DropTempTable();
        }

        public override System.Data.DataTable GetList(IRptParams filter)
        {
            var result = base.GetList(filter);
            return result;
        }

        public override List<long> GetOrgIdList(IRptParams filter)
        {
            var result = base.GetOrgIdList(filter);
            return result;
        }

        public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
        {
            var result = base.GetTreeNodes(filter);
            return result;
        }
    }
}